Snowflake上でのデータ変換処理の開発をGUIベースで行える「Coalesce」のQuickStartGuideを試してみた #SnowflakeDB

アドベントカレンダー2022

#Snowflake

さがら

2022.12.23

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の２３日目の記事となります。

さがらです。

Snowflake上でデータ変換処理の開発をGUIで行えるCoalesceのQuickStartGuideを試してみたので、その内容をまとめてみます。

Coalesceとは

Coalesceは、Snowflakeに特化したGUIのデータ変換処理のサービスです。

このツールの面白いところは、Snowflakeを用いた本格的なデータエンジニアリングに必要な以下のような機能をGUIベースで簡単に実装できる所にあります。具体的には、

ストリームとタスクを自動的に生成したChange Data Captureの実装
自動的にVARIANT型のカラムからJSONを抽出して、生成されたテーブルの各列にフラット化
Hubs、Links、Sat などの Data Vault オブジェクトを構築するためのすべての組み込みテンプレート
FactsやDimensionsなどの基本的なディメンショナルモデリングのオブジェクトの実装 (Slowly Changing Dimension Type-2に対応)

といったことが出来るようです！

上述の内容は下記の記事からの引用なのですが、この記事の執筆者はSnowflake界隈では超有名人のKent Graziano氏であり、Coalesce社のStrategic Advisorも兼任しているようです！このバックグラウンドもあり、モデリング周りの機能が充実しているのかもしれないですねｗ

そんな強力なアドバイザーもいるCoalesceを、今回試してみました。

アカウント登録

では、早速Coalesceのアカウント登録から始めていきます。

Coalesceは、1ユーザーで試す分にはなんと無料で開始することができます！（ノード数などに制約があるため注意）

こちらのページに移動し、名前などの情報を入れてGet Startedを押します。

すると、下図のようなメールが届くはずです。概要として、「後でアカウントと初期パスワードの情報を送るよ、最大12時間かかるよ。12時間経過してもアカウント情報が来なかったらメールしてね」と書いてあります。

時差の関係もあると思いますが、私の場合は「約2時間」でアカウント情報が掲載されたメールが届きました！

Coalesceのログインページで、このアカウント情報を入力し、ログインします。

初めてログインすると、下図のようにWorkspacesの画面が出てくると思います。この画面が出てくればOKです！

Quick Start Guideに沿って試してみる

ここからは、公式のQuick Start Guideに沿って、Coalesceがどんな事ができるのか、確かめてみます。

画面の確認

初めてログインすると、Workspacesの画面が出てきます。Workspaceは、実際にCoalesceを使って開発を行ったり処理を実行する環境となります。

デフォルトで用意されているDEVを選択してみます。

すると、Buildの画面が表示されました。Coalesceで開発を行う際は、このBuildの画面を使用することになります。

参考までに、Deploy画面では開発した処理を、別のQAや本番の環境にプッシュする際に使用する画面となります。

Snowflakeアカウントへの接続

続けて、Snowflakeのアカウントへ接続をしてみます。

CoalesceのBuildの画面から、左下の歯車マークを押します。

Build Settingsのタブが開くので、Development Workspaceから、使用するWorkspaceの右上の編集マークを押します。

まずSettingsの画面で、Account欄に対象のSnowflakeのアカウントのURLを入れます。

続けてUser Credentialsの画面で、Username、Passwordを入れます。RoleとWarehouseも必要に応じて入力しておきましょう。Test Connectionで接続テストを行い、問題ないことが確認できたら、右下のSaveを押します。

これで、対象のWorkspaceとSnowflakeアカウントを連携することが出来ました！

使用するデータベース・スキーマの設定

続いて、変換元のデータベース・スキーマと出力先のデータベース・スキーマを設定していきます。

まず、出力先のデータベース・スキーマを設定するためのStorage Locationを定義します。 Build Settingsのタブにおいて、New Storage Locationを押します。

続いて、表示されたポップアップにおいて、NameをTARGETと入力し、右下のCreateを押します。

作成したTARGETをデフォルトの出力先とするため、TARGETの編集マークを押し、表示されたポップアップでSet defaultを押します。

次に、変換元のデータベース・スキーマを定義するStorage LocationであるSRCと、出力先のデータベース・スキーマを定義するStorage LocationであるTARGETに対して、具体的にデータベースとスキーマを設定していきます。

Build SettingsのタブにおいてDevelopment Workspaceから、使用するWorkspaceの右上の編集マークを押します。

Storage Mappingsを押すと、SRCとTARGETに対して具体的にデータベースとスキーマを割り当てることが出来る画面が出てくるので、以下のように設定したあと、右下のSaveを押します。

SRC
- Quick Start Guideの設定に沿って、SNOWFLAKE_SAMPLE_DATAデータベースのTPCH_SF1スキーマを選択
TARGET
- 任意のデータベース・スキーマを選択（加工しデータが出力されるスキーマとなります。）

データ変換パイプラインを構築してみる

続いて、Coalesceを使ってデータ変換パイプラインを構築してみます！

Source Nodeの設定

データ変換する元となる「Source Node」を設定していきます。

画面左のNodesをクリックしNodesの画面を開いた上で、右上の「＋」マークからAdd Sourcesを押します。

表示されたポップアップで、追加したいテーブルを全て選択し、右下のAdd [選択した数] sourcesを押します。

すると、NodeとBrowserタブにおいて、選択したテーブルが表示されました。

Stage Nodeの設定

続いて、Source Nodeから最低限の加工を行う「Stage Node」を設定します。試しにここでは、Nationテーブルに対するStage Nodeを設定してみます。

Browserタブにおいて、NATIONで右クリックし、Add Nodeを押し、Stageを押します。

すると、STG_NATIONというStage Nodeが作られ、その編集画面が開かれると思います。

画面右側のNode Propertiesを押すと、このノードの出力先は先程作成したStorage LocationであるTARGETになっていることがわかります。

ここで、一度試しにテーブルを作成してみます。画面下部のCreateを押すと、対象のStage Nodeに該当するSTG_NATIONテーブルが作られます。

続いて、Runを押してみます。Runを押すと、対象のテーブルに対してデータがロードされます。

ただ、ここまでは何の加工も行っていませんので、少し加工処理を加えてみます。

画面上部にあるMappingタブから、N_NAMEのTransform列をダブルクリックし、LOWER({{SRC}})と入力します。これは、N_NAMEの値を小文字に変換する、というシンプルな加工処理を加えております。

この状態で枠外をクリックすると、N_NAMEのTransform列の値が、実際のSQLの構文に沿った形に変換されました。

この状態で、もう一度Runを押してこのStage Nodeを実行してみます！すると、N_NAME列が小文字に変換されたことがわかると思います。

「あれ、ここでRunしたら大文字のレコードと小文字のレコードがどちらもINSERTされてしまうのではないか？」と思い、Snowflakeの画面で履歴を確認してみましたが、対象のテーブルを一度TRUNCATEしてから、INSERTが行われていました。

ディメンションテーブルの作成

続いて、ディメンションテーブルを作成してみます。

なんと、Coalesceでは設定を少し行うだけSlowly Changing Dimension Type-2の形式で、ディメンションテーブルにおいて履歴データを保持することが可能です。

では、実際にやってみます。

まずはStage Nodeから作成していきます。Browserタブにおいて、CUSTOMERで右クリックし、Add Nodeを押し、Stageを押します。

ここでは、特別な加工を行わないため、CreateとRunを続けて押し、テーブルの作成とデータロードを行います。

続いて、ディメンションテーブルを定義していきます。Browserタブに戻り、STG_CUSTOMERで右クリックし、Add Nodeを押し、Dimensionを押します。

開かれたDIM_CUSTOMERの編集画面で、画面右側のOptionsから、Business Keyにおいて、C_CUSTKEYを選択し、「>」を押して設定します。

この後、下図のように右側にC_CUSTKEYが表示されていればOKです。

さらに続けて、画面右側のOptionsから、Change Trackingにおいて、C_ADDRESSとC_PHONEを選択し、「>」を押して設定します。

※このChange Trackingが設定されていないとSlowly Changing Dimension Type-1として動作し、何かしらカラムが設定されているとSlowly Changing Dimension Type-2として動作するようです。

こちらも設定後、右側にC_ADDRESSとC_PHONEが表示されていればOKです。

では、実際にCreateでテーブル作成を行い、Runでデータロードを行います。

実際に作成されたテーブルを見てみると、下図のように履歴として管理するためのカラムが追加されていました！Slowly Changing Dimension Type-2の実装がここまで簡単に出来るのはありがたいですね。

ファクトテーブルの作成

では、このQuick Start Guideの最後として、ファクトテーブルを作成してみます。

まずはStage Nodeから作成していきます。Browserタブに戻り、ORDERSで右クリックし、Add Nodeを押し、Stageを押します。

簡易的にファクトテーブルとして用いるために、キーに該当するカラムと実際の値を持つカラム以外を除外します。具体的には、O_ORDERKEY、O_CUSTKEY、O_TOTALPRICEの3つのカラムだけを残します。

Stage Nodesにおけるカラムの削除方法は、対象のカラムで右クリックをし、Delete Columnを押せばOKです。各カラムは、Ctrlキーなどで複数列まとめて選択も可能です。

下図のように3カラムだけになればOKです。

続いて、顧客情報に関するキー情報もファクトテーブルに含めるため、JOINの定義を行っていきます。

まず、画面左側から先程作成したDIM_CUSTOMERをクリックし、下部に表示されたDIM_CUSTOMER_KEYを、STG_ORDERSのカラム一覧にドラッグ＆ドロップで追加します。

STG_ORDERSのJoinタブに切り替えて、Generate Joinを押し、Copy to Editorを押します。

すると、Editor欄にコピーされますが、元々記述されていたFROM {{ ref('SRC', 'ORDERS') }} "ORDERS"が不要のため、こちらを削除します。

コピーした内容の中に/*COLUMN*/という表記がありますが、このままではJOINに用いるキーが合致しないため、これをO_CUSTKEYに書き換えます。

ここまで設定できたら、一度CreateとRunを実行します。

続いて、ファクトテーブルを定義していきます。Browserタブに戻り、STG_ORDERSで右クリックし、Add Nodeを押し、Factを押します。

FCT_ORDERSの編集画面が開いたら、画面右側のOptionsから、Business KeyにO_ORDERKEYを追加します。

この設定が出来たら、CreateとRunを押して、ファクトテーブルを作成します。

実際にSnowflakeから確認してみると、O_ORDRRKEYごとに注文額が記録されたファクトテーブルが作られていました！

作成したファクトテーブルとディメンションテーブルをJOINするクエリの実行

では、サンプルクエリとして、これまでに作成したテーブルを用いて以下のクエリを実行します。事前にコンテキストの設定を忘れないように注意です。

select DIM.C_NAME CUSTOMER_NAME,
sum(FCT.O_TOTALPRICE) TOTAL_PRICE
from "FCT_ORDERS" FCT
inner join "DIM_CUSTOMER" DIM
on FCT.DIM_CUSTOMER_KEY = DIM.DIM_CUSTOMER_KEY
group by DIM.C_NAME;

すると、このようにディメンションテーブルDIM_CUSTOMERに記録されたCUSTOMER_NAMEごとに、ファクトテーブルFCT_ORDERSに記録されたO_TOTALPRICEの合計値を示すTOTAL_PRICEが表示されました！